﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.Specialized;
using System.Data;
using System.Net;
using System.IO;
using System.Web;
using System.Reflection;

namespace Kerjin.Util.Google
{
	public class FusionTablesClient
	{
		private string _Message = "";//訊息
		private NameValueCollection _nvc = null;//認證資訊

		public FusionTablesClient()
		{
			_nvc = new NameValueCollection();
		}

		/// <summary>
		/// 物件傳遞給使用者的訊息
		/// </summary>>
		public string Message
		{
			get { return _Message; }
		}

		/// <summary>
		/// ClientLogin 取得存取Google Account帳戶的權限
		/// </summary>
		/// <param name="account">帳號</param>
		/// <param name="password">密碼</param>
		/// <returns>是否取得存取權限(true:是/false:否)</returns>
		public bool GetAuth(string account, string password)
		{
			_Message = "";
			try
			{
				#region "對API提出需求並取得回應"
				HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://www.google.com/accounts/ClientLogin");

				IWebProxy prxy = WebRequest.DefaultWebProxy;
				prxy.Credentials = CredentialCache.DefaultCredentials;
				req.Proxy = prxy;

				req.Method = "POST";
				req.ContentType = "application/x-www-form-urlencoded";
				string strPostVaule = "accountType=GOOGLE&Email=" + account + "&Passwd=" + password + "&service=fusiontables&source=TheDataStewards-DataSyncr-1.05";
				req.ContentLength = strPostVaule.Length;


				StreamWriter swRequest = new StreamWriter(req.GetRequestStream(), Encoding.ASCII);
				swRequest.Write(strPostVaule);
				swRequest.Close();
				swRequest.Dispose();
				strPostVaule = null;

				StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
				string strResponse = srResponse.ReadToEnd();
				srResponse.Close();
				srResponse.Dispose();

				prxy = null;
				req = null;
				#endregion

				#region "解析回傳資料"
				string[] aryReturn = strResponse.Split('\n');
				strResponse = null;
				try
				{
					foreach (string strReturn in aryReturn)
					{
						string[] aryNameValue = strReturn.Split('=');
						if (aryNameValue.Length == 2)
						{
							_nvc.Add(aryNameValue[0], aryNameValue[1]);
						}
					}
					_Message = "成功取得認證。";
				}
				catch (Exception exp)
				{
					throw exp;
				}
				finally
				{
					aryReturn = null;
				}
				#endregion
				return true;
			}
			catch (Exception exp)
			{
				_Message = "取得認證失敗(" + exp.Message + ")。";
				return false;
			}
		}

		/// <summary>
		/// 建立資料表
		/// </summary>
		/// <param name="tableName">資料表名稱</param>
		/// <param name="fieldInfo">欄位資訊(欄名:型態)</param>
		/// <returns>資料表編號</returns>
        public string CreateTable(string tableName, List<string> fieldInfo)
		{
			_Message = "";
			string strTableID = "";
			try
			{
				#region "對API提出需求並取得回應"
				HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://www.google.com/fusiontables/api/query");

				IWebProxy prxy = WebRequest.DefaultWebProxy;
				prxy.Credentials = CredentialCache.DefaultCredentials;
				req.Proxy = prxy;

				req.Method = "POST";
				req.ContentType = "application/x-www-form-urlencoded";
				req.Headers.Add("Authorization", "GoogleLogin auth=" + _nvc["Auth"]);

				string strSql = "CREATE TABLE '" + tableName + "' (" + string.Join(",", fieldInfo.ToArray()) + ")";
				string strPostValue = "sql=" + HttpUtility.UrlEncode(strSql);
				strSql = null;
				req.ContentLength = strPostValue.Length;

				StreamWriter swRequest = new StreamWriter(req.GetRequestStream(), Encoding.ASCII);
				swRequest.Write(strPostValue);
				swRequest.Close();
				swRequest.Dispose();
				strPostValue = null;

				StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
				string strResponse = srResponse.ReadToEnd();
				srResponse.Close();
				srResponse.Dispose();

				prxy = null;
				req = null;
				#endregion

				#region "解析回傳資料"
				string[] aryReturn = strResponse.Split('\n');
				strResponse = null;
				try
				{
					if (aryReturn.Length > 2)
					{
						strTableID = aryReturn[1];
					}
					_Message = "已成功建立資料表, 資料表編號為 " + strTableID + "。";
				}
				catch (Exception exp)
				{
					throw exp;
				}
				finally
				{
					aryReturn = null;
				}
				#endregion
			}
			catch (Exception exp)
			{
				_Message = "建立資料表失敗(" + exp.Message + ")。";
			}
			return strTableID;
		}

		/// <summary>
		/// 刪除資料表
		/// </summary>
		/// <param name="tableID">資料表編號</param>
		public bool DropTable(string tableID)
		{
			_Message = "";
			try
			{
				#region "對API提出需求並取得回應"
				HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://www.google.com/fusiontables/api/query");

				IWebProxy prxy = WebRequest.DefaultWebProxy;
				prxy.Credentials = CredentialCache.DefaultCredentials;
				req.Proxy = prxy;

				req.Method = "POST";
				req.ContentType = "application/x-www-form-urlencoded";
				req.Headers.Add("Authorization", "GoogleLogin auth=" + _nvc["Auth"]);

				string strPostValue = "sql=" + HttpUtility.UrlEncode("DROP TABLE " + tableID);
				req.ContentLength = strPostValue.Length;

				StreamWriter swRequest = new StreamWriter(req.GetRequestStream(), Encoding.ASCII);
				swRequest.Write(strPostValue);
				swRequest.Close();
				swRequest.Dispose();
				strPostValue = null;

				StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
				string strResponse = srResponse.ReadToEnd();
				srResponse.Close();
				srResponse.Dispose();

				prxy = null;
				req = null;
				#endregion

				#region "解析回傳資料"
				string[] aryReturn = strResponse.Split('\n');
				strResponse = null;
				try
				{
					if (aryReturn.Length > 0)
					{
						if (aryReturn[0] == "OK")
						{
							_Message = "已成功刪除資料表(" + tableID + ")。";
						}
						else
						{
							throw new Exception(aryReturn[0]);
						}
					}
				}
				catch (Exception exp)
				{
					throw exp;	
				}
				finally
				{
					aryReturn = null;
				}
				#endregion
				return true;
			}
			catch (Exception exp)
			{
				_Message = "刪除資料表失敗(" + exp.Message + ")。";
				return false;
			}
		}

		/// <summary>
		/// 取得資料表機碼
		/// </summary>
		/// <param name="tableID">資料表編號</param>
		/// <returns>資料表機碼訊息</returns>
		public DataTable GetTableShema(string tableID)
		{
			_Message = "";
			DataTable dtb = new DataTable();
			#region "Init Result Data Table"
			dtb.Columns.Add(new DataColumn("COLUMN_ID", Type.GetType("System.String")));
			dtb.Columns.Add(new DataColumn("COLUMN_NAME", Type.GetType("System.String")));
			dtb.Columns.Add(new DataColumn("DATA_TYPE", Type.GetType("System.String")));
			#endregion
			try
			{
				#region "對API提出需求並取得回應"
				HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://www.google.com/fusiontables/api/query?sql=DESCRIBE+" + tableID + "");

				IWebProxy prxy = WebRequest.DefaultWebProxy;
				prxy.Credentials = CredentialCache.DefaultCredentials;
				req.Proxy = prxy;

				req.Method = "GET";
				req.ContentType = "application/x-www-form-urlencoded";
				req.Headers.Add("Authorization", "GoogleLogin auth=" + _nvc["Auth"]);

				StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
				string strResponse = srResponse.ReadToEnd();
				srResponse.Close();
				srResponse.Dispose();

				prxy = null;
				req = null;
				#endregion

				#region "解析回傳資料"
				string[] aryReturn = strResponse.Split('\n');
				strResponse = null;
				try
				{
					if (aryReturn.Length > 0)
					{
						for (int iReturn = 1; iReturn < (aryReturn.Length - 1); iReturn++)
						{
							string[] aryData = aryReturn[iReturn].Split(',');
							if (aryData.Length == 3)
							{
								DataRow dr = dtb.NewRow();
								dr[0] = aryData[0];
								dr[1] = aryData[1];
								dr[2] = aryData[2];
								dtb.Rows.Add(dr);
							}
						}
					}
					_Message = "已成功取得資料表機碼。";
				}
				catch (Exception exp)
				{
					throw exp;
				}
				finally
				{
					aryReturn = null;
				}
				#endregion
			}
			catch (Exception exp)
			{
				_Message = "取得資料表機碼失敗(" + exp.Message + ")。";
			}
			return dtb;
		}

		/// <summary>
		/// 取得資料表清單
		/// </summary>
		/// <returns>資料表清單</returns>
		public DataTable GetTableList()
		{
			_Message = "";
			DataTable dtb = new DataTable();
			#region "Init Result Data Table"
			dtb.Columns.Add(new DataColumn("TABLE_ID", Type.GetType("System.String")));
			dtb.Columns.Add(new DataColumn("TABLE_NAME", Type.GetType("System.String")));
			#endregion
			try
			{
				#region "對API提出需求並取得回應"
				HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://www.google.com/fusiontables/api/query?sql=SHOW+TABLES&encid=false");

				IWebProxy prxy = WebRequest.DefaultWebProxy;
				prxy.Credentials = CredentialCache.DefaultCredentials;
				req.Proxy = prxy;

				req.Method = "GET";
				req.ContentType = "application/x-www-form-urlencoded";
				req.Headers.Add("Authorization", "GoogleLogin auth=" + _nvc["Auth"]);

				StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
				string strResponse = srResponse.ReadToEnd();
				srResponse.Close();
				srResponse.Dispose();

				prxy = null;
				req = null;
				#endregion

				#region "解析回傳資料"
				string[] aryReturn = strResponse.Split('\n');
				strResponse = null;
				try
				{
					if (aryReturn.Length > 0)
					{
						for (int iReturn = 1; iReturn < (aryReturn.Length - 1); iReturn++)
						{
							string[] aryData = aryReturn[iReturn].Split(',');
							if (aryData.Length == 2)
							{
								DataRow dr = dtb.NewRow();
								dr[0] = aryData[0];
								dr[1] = aryData[1];
								dtb.Rows.Add(dr);
							}
						}
					}
					_Message = "已成功取得資料表清單。";
				}
				catch (Exception exp)
				{
					throw exp;
				}
				finally
				{
					aryReturn = null;
				}
				#endregion
			}
			catch (Exception exp)
			{
				_Message = "取得資料表清單失敗(" + exp.Message + ")。";
			}
			return dtb;
		}

		/// <summary>
		/// 執行敘述
		/// </summary>
		/// <param name="sql">敘述</param>
		public void ExecuteNonQuery(string sql)
		{
			_Message = "";
			try
			{
				#region "對API提出需求並取得回應"
				HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://www.google.com/fusiontables/api/query");

				IWebProxy prxy = WebRequest.DefaultWebProxy;
				prxy.Credentials = CredentialCache.DefaultCredentials;
				req.Proxy = prxy;

				req.Method = "POST";
				req.ContentType = "application/x-www-form-urlencoded";
				req.Headers.Add("Authorization", "GoogleLogin auth=" + _nvc["Auth"]);
				string strPostValue = "sql=" + HttpUtility.UrlEncode(sql);
				req.ContentLength = strPostValue.Length;

				StreamWriter swRequest = new StreamWriter(req.GetRequestStream(), Encoding.ASCII);
				swRequest.Write(strPostValue);
				swRequest.Close();
				swRequest.Dispose();
				strPostValue = null;

				StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
				string strResponse = srResponse.ReadToEnd();
				srResponse.Close();
				srResponse.Dispose();

				prxy = null;
				req = null;
				#endregion

				_Message = strResponse;
			}
			catch (Exception exp)
			{
				_Message = "執行敘述失敗(" + exp.Message + ")。";
			}
		}

		/// <summary>
		/// 執行敘述並回傳資料
		/// </summary>
		/// <param name="sql">敘述</param>
		/// <returns>資料</returns>
		public DataTable ExecuteDataTable(string sql)
		{
			_Message = "";
			DataTable dtb = new DataTable();
			try
			{
				#region "對API提出需求並取得回應"
				HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://www.google.com/fusiontables/api/query?sql=" + HttpUtility.UrlEncode(sql));

				IWebProxy prxy = WebRequest.DefaultWebProxy;
				prxy.Credentials = CredentialCache.DefaultCredentials;
				req.Proxy = prxy;

				req.Method = "GET";
				req.ContentType = "application/x-www-form-urlencoded";
				req.Headers.Add("Authorization", "GoogleLogin auth=" + _nvc["Auth"]);

				StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
				string strResponse = srResponse.ReadToEnd();
				srResponse.Close();
				srResponse.Dispose();

				prxy = null;
				req = null;
				#endregion

				#region "解析回傳資料"
				string[] aryReturn = strResponse.Split('\n');
				strResponse = null;
				try
				{
					if (aryReturn.Length > 2)
					{
						string[] aryField = aryReturn[0].Split(',');
						int intFieldCount = aryField.Length;
						foreach (string strFieldName in aryField)
						{
							dtb.Columns.Add(new DataColumn(strFieldName, Type.GetType("System.String")));
						}

						for (int iReturn = 1; iReturn < (aryReturn.Length - 1); iReturn++)
						{
							string[] aryData = aryReturn[iReturn].Split(',');
							if (aryData.Length > 0)
							{
								DataRow dr = dtb.NewRow();
								for (int intField = 0; intField < intFieldCount; intField++)
								{
									dr[intField] = aryData[intField];
								}
								dtb.Rows.Add(dr);
							}
							else
							{
								throw new Exception("欄位數量不一致");
							}
						}
					}
					_Message = "已成功取得資料。";
				}
				catch (Exception exp)
				{
					throw exp;
				}
				finally
				{
					aryReturn = null;
				}
				#endregion
			}
			catch (Exception exp)
			{
				_Message = "取得資料失敗(" + exp.Message + ")。";
			}
			return dtb;
		}

		/// <summary>
		/// 取得資料
		/// </summary>
		/// <param name="tableID">資料表編號</param>
		/// <param name="fieldName">要顯示的欄位</param>
		/// <returns>資料</returns>
		public DataTable GetData(string tableID, List<string> fieldName)
		{
			_Message = "";
			DataTable dtb = new DataTable();
			try
			{
				string strSql = "";
				if (fieldName == null)
				{
					strSql = "SELECT * FROM " + tableID;
				}
				else
				{
					List<string> lstFieldName = new List<string>();
					foreach (string strFieldName in fieldName)
					{
						lstFieldName.Add("'" + strFieldName + "'");
					}
					strSql = "SELECT " + string.Join(",", lstFieldName.ToArray()) + " FROM " + tableID;
				}

				#region "對API提出需求並取得回應"
				HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://www.google.com/fusiontables/api/query?sql=" + HttpUtility.UrlEncode(strSql));
				strSql = null;

				IWebProxy prxy = WebRequest.DefaultWebProxy;
				prxy.Credentials = CredentialCache.DefaultCredentials;
				req.Proxy = prxy;

				req.Method = "GET";
				req.ContentType = "application/x-www-form-urlencoded";
				req.Headers.Add("Authorization", "GoogleLogin auth=" + _nvc["Auth"]);

				StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
				string strResponse = srResponse.ReadToEnd();
				srResponse.Close();
				srResponse.Dispose();

				prxy = null;
				req = null;
				#endregion

				#region "解析回傳資料"
				string[] aryReturn = strResponse.Split('\n');
				strResponse = null;
				try
				{
					if (aryReturn.Length > 2)
					{
						string[] aryField = aryReturn[0].Split(',');
						int intFieldCount = aryField.Length;
						foreach (string strFieldName in aryField)
						{
							dtb.Columns.Add(new DataColumn(strFieldName, Type.GetType("System.String")));
						}

						for (int iReturn = 1; iReturn < (aryReturn.Length - 1); iReturn++)
						{
							string[] aryData = aryReturn[iReturn].Split(',');
							if (aryData.Length > 0)
							{
								DataRow dr = dtb.NewRow();
								for (int intField = 0; intField < intFieldCount; intField++)
								{
									dr[intField] = aryData[intField];
								}
								dtb.Rows.Add(dr);
							}
							else
							{
								throw new Exception("欄位數量不一致");
							}
						}
					}
					_Message = "已成功取得資料。";
				}
				catch (Exception exp)
				{
					throw exp;
				}
				finally
				{
					aryReturn = null;
				}
				#endregion
			}
			catch (Exception exp)
			{
				_Message = "取得資料失敗(" + exp.Message + ")。";
			}
			return dtb;
		}

		/// <summary>
		/// 物件解構
		/// </summary>
		public void Dispose()
		{
			_nvc.Clear();
			_nvc = null;
			_Message = null;
		}

	}
}
